/**
 * Copyright (c) 2010 Abadi Kurniawan, Brian Dukes
 * Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"),
 * to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense,
 * and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:
 * 
 * The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. 
 * 
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF
 * MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM,
 * DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE 
 * USE OR OTHER DEALINGS IN THE SOFTWARE.
 */
package hackathon.android.mymessageinbox;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;

/**
 * @author Abadi Kurniawan, Brian Dukes
 *
 */
public class MyDatabase  {
	
	private static final String TABLE_NAME = "MyMessage"; 
    private static final String DATABASE_NAME = "dnnmymessagesinbox.db";
    private static final int DATABASE_VERSION = 2;

    private DatabaseHelper dbHelper;
    private SQLiteDatabase db;

    private SQLiteStatement insertStmt;
    private static final String INSERT = "insert into "
        + TABLE_NAME + "("
        + Message.MESSAGE_ID+","
        + Message.ALLOW_REPLY+","
        + Message.BODY + ","
        + Message.CONVERSATION + " ,"
        + Message.DATE + " ,"
        + Message.SENDER + " ,"
        + Message.STATUS + " ,"
        + Message.SUBJECT + " ,"
        + Message.TO_ROLE + " ,"
        + Message.TO_USER_NAME + ","
        + Message.OWNER
        +") values (?,?,?,?,?,?,?,?,?,?,?)";

    
	public MyDatabase(Context context) {
		this.dbHelper = new DatabaseHelper(context);
		this.db = dbHelper.getWritableDatabase();
		this.insertStmt = this.db.compileStatement(INSERT);
	}
	
	   public long insert(Message msg) {
	      this.insertStmt.bindLong(1, msg.getMessageId());
	      this.insertStmt.bindLong(2, msg.isAllowReply()?1:0);
	      this.insertStmt.bindString(3, msg.getBody());
	      this.insertStmt.bindString(4, msg.getConversation());
	      this.insertStmt.bindLong(5, msg.getDate().getTime());
	      this.insertStmt.bindString(6, msg.getSender());
	      this.insertStmt.bindString(7, msg.getStatus());
	      this.insertStmt.bindString(8, msg.getSubject());
	      this.insertStmt.bindString(9, msg.getToRole());
	      this.insertStmt.bindString(10, msg.getToUserName());
	      this.insertStmt.bindString(11, msg.getOwner());
	      return this.insertStmt.executeInsert();
	   }

	   public void deleteAll() {
	      this.db.delete(TABLE_NAME, null, null);
	   }

	   public List<Message> selectAll(String userName) {
	      List<Message> list = new ArrayList<Message>();
	      Cursor cursor = this.db.query(TABLE_NAME, new String[] { 
	    		  Message.MESSAGE_ID,
	    	      Message.ALLOW_REPLY,
	    	      Message.BODY,
	    	      Message.CONVERSATION,
	    	      Message.DATE,
	    	      Message.SENDER,
	    	      Message.STATUS,
	    	      Message.SUBJECT,
	    	      Message.TO_ROLE,
	    	      Message.TO_USER_NAME
	    		  }, 
	        Message.OWNER+" = '"+userName+"'", null, null, null, Message.DATE+" desc");
	      try {
	    	  if (cursor.moveToFirst()) {
	    		  do {
	    			  Message msg = new Message();
	    			  msg.setMessageId(cursor.getInt(0));
	    			  msg.setAllowReply(cursor.getInt(1)==1);
	    			  msg.setBody(cursor.getString(2));
	    			  msg.setConversation(cursor.getString(3));
	    			  msg.setDate(new Date(cursor.getLong(4)));
	    			  msg.setSender(cursor.getString(5));
	    			  msg.setStatus(cursor.getString(6));
	    			  msg.setSubject(cursor.getString(7));
	    			  msg.setToRole(cursor.getString(8));
	    			  msg.setToUserName(cursor.getString(9));
	    			  list.add(msg); 
	    		  } while (cursor.moveToNext());
	    	  }
	      }finally {
	    	  if (cursor != null && !cursor.isClosed()) {
	    		  cursor.close();
	    	  }	
	      }
	      return list;
	   }
	   
	   public void close()
	   {
		   this.insertStmt.close();
		   this.db.close();
	   }

	private class DatabaseHelper extends SQLiteOpenHelper {
		
		DatabaseHelper(Context context) {
			super(context, DATABASE_NAME, null, DATABASE_VERSION);
		}
		
		@Override
		public void onCreate(SQLiteDatabase db) {
			 db.execSQL("CREATE TABLE " + TABLE_NAME + " ("
	                 + Message.MESSAGE_ID + " INTEGER PRIMARY KEY,"
	                 + Message.ALLOW_REPLY + " INTEGER,"
	                 + Message.BODY + " TEXT,"
	                 + Message.CONVERSATION + " TEXT,"
	                 + Message.DATE + " INTEGER,"
	                 + Message.SENDER + " TEXT,"
	                 + Message.STATUS + " TEXT,"
	                 + Message.SUBJECT + " TEXT,"
	                 + Message.TO_ROLE + " TEXT,"
	                 + Message.TO_USER_NAME + " TEXT,"
	                 + Message.OWNER + " TEXT"
	                 + ");");
		}
	
		@Override
		public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
	        db.execSQL("DROP TABLE IF EXISTS "+ TABLE_NAME);
	        onCreate(db);
		}
	}
}
